﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Log.Log;
using OfficeOpenXml;
using System.IO;
using CsvHelper;
using System.ComponentModel;
using System.Threading;
using System.Data.SQLite;
using System.Collections.ObjectModel;

namespace FixtureManageSystem.Models
{

    class DataExportService
    {
        /// <summary>
        /// 表格类型
        /// </summary>
        private string tableType;
        private readonly SqliteHelper sqliteHelper = new SqliteHelper();

        /// <summary>
        /// 导出数据库数据到目标路径
        /// </summary>
        /// <param name="path"></param>
        public void Export(string path, string tableType, BackgroundWorker sender)
        {
            File.WriteAllText(path, "");
            // 其实这里可以写成是委托类型，但是没必要，代码量差不多
            switch (tableType)
            {
                case "夹具表":
                    ExportFixture(path, sender);
                    break;
                case "人员表":
                    ExportEmployee(path, sender);
                    break;
                case "借用归还表":
                    ExportBorrow(path, sender);
                    break;
                case "入库表":
                    ExportWareHouseIn(path, sender);
                    break;
                default:
                    return;
            }
            sender.ReportProgress(100);
            // 数据生成完毕后打开导出数据所在文件夹
            string parentPath = Path.GetDirectoryName(path);
            System.Diagnostics.Process.Start(parentPath);
        }
  
        public int GetCount(string tableType)
        {
            // 创建一变量来存储员工数量
            int count;
            // 创建要执行的Sql 语句
            string str = $"SELECT COUNT(*) as totalNumber FROM {tableType}";
            SQLiteDataReader Sdr = sqliteHelper.read(str);

            // 4、读取结果的数据
            while (Sdr.Read())
            {
                count = int.Parse(Sdr["totalNumber"].ToString());
                return count;
            }

            return 0;
        }

        /// <summary>
        /// 导出夹具表
        /// </summary>
        /// <param name="path"></param>
        /// <param name="sender"></param>
        public void ExportFixture(string path, BackgroundWorker sender)
        {
            tableType = "Fixture";
            // 初始化返回值
            int total_count = GetCount(tableType);
            int num = 0;
            sender.ReportProgress(num);
            new LogFactory().GetLog("logger").Info(true, total_count);
            // 等待查询总数返回结果

            if (total_count == 0)
            {
                num = 100;
                sender.ReportProgress(num);
            }

            else
            {

                // 创建要执行的Sql语句
                string str = $"select * from {tableType};";
                SQLiteDataReader Sdr = sqliteHelper.read(str);
                int count = 0;
                while (Sdr.Read())
                {
                    using (var writer = new StreamWriter(path, true))
                    {

                        using (var csv = new CsvWriter(writer))
                        {
                            count++;
                            num = (int)(Math.Round((double)count / total_count, 2) * 100);
                            sender.ReportProgress(num);
                            Console.WriteLine($"num = {num}");
                            Fixture newFixture = new Fixture()
                            {
                                BarCode = Sdr["BarCode"].ToString(),
                                Name = Sdr["Name"].ToString(),
                                MaterialType = Sdr["MaterialType"].ToString(),
                                Num = Convert.ToInt32(Sdr["Num"]),
                                Position = Sdr["Position"].ToString(),
                            };
                            csv.WriteRecord(newFixture);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 导出人员表
        /// </summary>
        /// <param name="path"></param>
        /// <param name="sender"></param>
        public void ExportEmployee(string path, BackgroundWorker sender)
        {
            tableType = "Employee";
            // 初始化返回值
            int total_count = GetCount(tableType);
            int num = 0;
            sender.ReportProgress(num);
            new LogFactory().GetLog("logger").Info(true, total_count);
            // 等待查询总数返回结果

            if (total_count == 0)
            {
                num = 100;
                sender.ReportProgress(num);
            }

            else
            {

                // 创建要执行的Sql语句
                string str = $"select * from {tableType};";
                SQLiteDataReader Sdr = sqliteHelper.read(str);
                int count = 0;
                while (Sdr.Read())
                {
                    using (var writer = new StreamWriter(path, true))
                    {

                        using (var csv = new CsvWriter(writer))
                        {
                            
                            count++;
                            num = (int)(Math.Round((double)count / total_count, 2) * 100);
                            sender.ReportProgress(num);
                            Employee newEmployee = new Employee()
                            {
                                PersonNo = Sdr["PersonNo"].ToString(),
                                Name = Sdr["Name"].ToString(),
                                Department = Sdr["Department"].ToString(),
                                Factory = Sdr["Factory"].ToString(),
                                Tel = Sdr["Tel"].ToString()
                            };
                            csv.WriteRecord(newEmployee);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 导出借用表
        /// </summary>
        /// <param name="path"></param>
        /// <param name="sender"></param>
        public void ExportBorrow(string path, BackgroundWorker sender)
        {
            tableType = "ReturnFixture";
            // 初始化返回值
            int total_count = GetCount(tableType);
            int num = 0;
            sender.ReportProgress(num);
            new LogFactory().GetLog("logger").Info(true, total_count);
            // 等待查询总数返回结果

            if (total_count == 0)
            {
                num = 100;
                sender.ReportProgress(num);
            }

            else
            {

                // 创建要执行的Sql语句
                string str = "SELECT ReturnFixture.*, Employee.Name as PersonName, fixture.*,  fixture.Num as fixtureNum FROM " +
                    "ReturnFixture JOIN fixture On ReturnFixture.BarCode = fixture.BarCode " +
                    "JOIN Employee on Employee.PersonNo = ReturnFixture.PersonNo;";
                SQLiteDataReader Sdr = sqliteHelper.read(str);
                int count = 0;
                while (Sdr.Read())
                {
                    using (var writer = new StreamWriter(path, true))
                    {

                        using (var csv = new CsvWriter(writer))
                        {
                            count++;
                            num = (int)(Math.Round((double)count / total_count, 2) * 100);
                            sender.ReportProgress(num);
                            Borrow borrow = new Borrow()
                            {
                                Id = Convert.ToInt32(Sdr["Id"]),
                                BarCode = Sdr["BarCode"].ToString(), // 条形码
                                Name = Sdr["Name"].ToString(), // 夹具名称
                                MaterialType = Sdr["MaterialType"].ToString(), // 物料类型
                                Num = Convert.ToInt32(Sdr["Num"]), // 借用或归还数量
                                Position = Sdr["Position"].ToString(),
                                PersonNo = Sdr["PersonNo"].ToString(), // 责任人工号
                                PersonName = Sdr["PersonName"].ToString(),  // 责任人姓名
                                UpdateTime = DateTime.Parse(Sdr["UpdateTime"].ToString()),
                                Comment = Sdr["Comment"].ToString(),
                                Status = Sdr["Status"].ToString(),
                            };
                            csv.WriteRecord(borrow);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 导出归还表
        /// </summary>
        /// <param name="path"></param>
        /// <param name="sender"></param>
        public void ExportReturn(string path, BackgroundWorker sender)
        {
            tableType = "ReturnFixture";
            // 初始化返回值
            int total_count = GetCount(tableType);
            int num = 0;
            sender.ReportProgress(num);
            new LogFactory().GetLog("logger").Info(true, total_count);
            // 等待查询总数返回结果

            if (total_count == 0)
            {
                num = 100;
                sender.ReportProgress(num);
            }

            else
            {

                // 创建要执行的Sql语句
                string str = $"select * from {tableType};";
                SQLiteDataReader Sdr = sqliteHelper.read(str);
                // 打开数据库
                int count = 0;
                while (Sdr.Read())
                {
                    using (var writer = new StreamWriter(path, true))
                    {

                        using (var csv = new CsvWriter(writer))
                        {
                            count++;
                            num = (int)(Math.Round((double)count / total_count, 2) * 100);
                            sender.ReportProgress(num);
                            Return newReturn = new Return()
                            {
                                BarCode = Sdr["BarCode"].ToString(), // 条形码
                                Num = Convert.ToInt32(Sdr["Num"]), // 借用或者归还数量
                                PersonNo = Sdr["PersonNo"].ToString(), // 归还人
                                ReturnTime = Convert.ToDateTime(Sdr["ReturnTime"]) // 预计归还时间
                            };
                            csv.WriteRecord(newReturn);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 导出入库表
        /// </summary>
        /// <param name="path"></param>
        /// <param name="sender"></param>
        public void ExportWareHouseIn(string path, BackgroundWorker sender)
        {
            tableType = "WareHouseIn";
            // 初始化返回值
            int total_count = GetCount(tableType);
            int num = 0;
            sender.ReportProgress(num);
            new LogFactory().GetLog("logger").Info(true, total_count);
            // 等待查询总数返回结果

            if (total_count == 0)
            {
                num = 100;
                sender.ReportProgress(num);
            }

            else
            {

                string str = "select WareHouseIn.id as Id, InTime, WareHouseIn.PersonNo as InPersonNo, WareHouseIn.Num as InNum , Fixture.*, Employee.Name as PersonName from WareHouseIn "
                + " Join fixture on WareHouseIn.BarCode = Fixture.BarCode " 
                + " Join Employee on  Employee.PersonNo = WareHouseIn.PersonNo;";
                SQLiteDataReader Sdr = sqliteHelper.read(str);
                // 打开数据库
                int count = 0;
                while (Sdr.Read())
                {
                    using (var writer = new StreamWriter(path, true))
                    {

                        using (var csv = new CsvWriter(writer))
                        {
                            count++;
                            num = (int)(Math.Round((double)count / total_count, 2) * 100);
                            sender.ReportProgress(num);

                            WareHouseIn wareHouseIn = new WareHouseIn()
                            {
                                Id = Convert.ToInt32(Sdr["Id"]),
                                BarCode = Sdr["BarCode"].ToString(), // 条形码
                                Name = Sdr["Name"].ToString(), // 夹具名称
                                MaterialType = Sdr["MaterialType"].ToString(), // 物料类型
                                Num = Convert.ToInt32(Sdr["InNum"]), // 入库数量
                                Position = Sdr["Position"].ToString(),
                                PersonNo = Sdr["InPersonNo"].ToString(), // 责任人
                                PersonName = Sdr["PersonName"].ToString(),
                                InTime = Convert.ToDateTime(Sdr["InTime"])  // 出库时间
                            };
                            csv.WriteRecord(wareHouseIn);
                        }
                    }
                }
            }
        }

        public ObservableCollection<Fixture> Search(Fixture ft)
        {
            ObservableCollection<Fixture> result = new ObservableCollection<Fixture>();
            string Name = ft.Name;
            string barcode = ft.BarCode;
            string MaterialType = ft.MaterialType;

            // 创建要执行的Sql 语句
            string str = $"select * from Fixture Where ";
            List<string> conditions = new List<string>();
            if (!string.IsNullOrEmpty(Name))
            {
                conditions.Add($"Fixture.Name Like '{Name}' ");
            }
            if (!string.IsNullOrEmpty(barcode))
            {
                conditions.Add($" Fixture.BarCode Like '{barcode}' ");
            }
            if (!string.IsNullOrEmpty(MaterialType))
            {
                conditions.Add($" Fixture.Barcode = '{MaterialType}' ");
            }

            if (conditions.Count == 0)
            {
                return result;
            }
            str += string.Join(" AND ", conditions);

            SQLiteDataReader Sdr = sqliteHelper.read(str);

            int count = 0;
            // 4、读取结果的数据
            while (Sdr.Read())
            {

                result.Add(new Fixture()
                {
                    BarCode = Sdr["BarCode"].ToString(),
                    Name = Sdr["Name"].ToString(),
                    MaterialType = Sdr["MaterialType"].ToString(),
                    Position = Sdr["Position"].ToString(),
                    Num = Convert.ToInt32(Sdr["Num"]),
                });
                count += 1;
            }
            return result;
        }



    }

}
